Example 3: Table

This example extends the previous two by including references to fields that can accept more than one value, in other words, tables. In this screenshot of the Parties module, we see that the Other Names: (Person Details) field can take multiple values:

Other Names: (Person Details) is a table of values (a column) and each row can hold a name. Its back-end field name further identifies it as a table with the addition of a _tab suffix:

NamOtherNames_tab

In this example we create three Person Party records that include Role and Other Name values (both of which are tables).

The fields loaded are:

Fields

Back-end name
First name NamFirst
Middle name NamMiddle
Last name NamLast
Party Type NamPartyType
Roles NamRoles_tab

 

 
Other Names NamOtherNames_tab

For display purposes only, the import data below is presented with column headings listed vertically rather than horizontally. The first row of any tab or comma delimited file must include the column names. The appropriate layout is:

NamPartyType NamFirst NamMiddle NamLast
Person Joe J Jackson
Person Michael   Williamson
Organisation      

The import data is:

Column Name (must appear as the first row of the import data file)

Record 1

Record 2

Record 3
NamPartyType Person Person Person
NamFirst Joe Michael Wilbur
NamMiddle J    
NamLast Jackson Williamson Withers
NamRoles_tab(1) Trainer    
NamRoles_tab(2) Teacher   Musician
NamOtherNames_tab(1) Joey Mike  
NamOtherNames_tab(2)   Willo  
NamOtherNames_tab(3)   Willy  

To specify the first row in a table the format is:

TableName_tab(1)

And the second row is:

TableName_tab(2)

And so on.

In this example three records would be created (or updated). For each record, the Roles table would have up to two entries (two rows) and the Other Names table would have up to three rows.

Updating records that include table fields

The Import tool can be used to update existing records in Vitalware (details here). Be aware of the following however:

Note: The entire table is replaced when the Import tool is used.

  1. Notice that in this TSV/CSV file three rows are specified for NamOtherNames_tab. The record for Joe Jackson (Record 1) only contains a value in NamOtherNames_tab(1), while NamOtherNames_tab(2) and NamOtherNames_tab(3) are blank.

    If there was already a record for Joe Jackson in Vitalware and it had a value in NamOtherNames_tab(1) and another value in NamOtherNames_tab(2), the value in NamOtherNames_tab(2) would be overwritten when this data file was imported.

  2. Consider also that if this data file was imported, the record for Michael Williamson (Record 2) would have three rows in the Other Names table.

    If you then updated these records with the following data file (i.e. excluding NamOtherNames_tab(3)):

NamPartyType

NamFirst

NamMiddle

NamLast

NamRoles_tab(1)

NamRoles_tab(2)

NamOtherNames_tab(1)

NamOtherNames_tab(2)

Person

Joe

J

Jackson

Trainer

Teacher

Joey

 

Person

Michael

 

Williamson

 

 

Mike

Willo

Person

Wilbur

 

Withers

 

Musician

 

 

the existing value in NamOtherNames_tab(3) would be lost.

The XML for this example is:

<?xml version="1.0" encoding="UTF-8"?>
<table>
	<!--First record-->
	<!--As well as including atomic references for single values, a tuple (which as we've seen defines a single record) can also include a table reference-->
	<tuple>
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">Joe</atom>
		<atom name="NamMiddle">J</atom>
		<atom name="NamLast">Jackson</atom>
		<!--The table is referenced using table tags-->
		<table name="NamRoles_tab">
			<!--A table element is used to represent a table of values. A value may be atomic, a tuple or another table. Each row in the table is defined by a tuple. Note that the data structure for tables in Vitalware only allows one value per tuple. One value could be an atom, tuple or a table (demonstrated in Example 8). In other words, a tuple could not contain two atoms. Each atom would need to be defined by its own tuple (as in this example). In this example, two rows are added to the Roles table-->
			<tuple>
				<atom>Trainer</atom>
			</tuple>
			<tuple>
				<atom>Teacher</atom>
			</tuple>
		</table>
		<!--In this example, one row is added to the Other Names table-->
		<table name="NamOtherNames_tab">
			<tuple>
				<atom>Joey</atom>
			</tuple>
		</table>
	</tuple>
	<!--Second record-->
	<tuple>
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">Michael</atom>
		<atom name="NamLast">Williamson</atom>
		<table name="NamOtherNames_tab">
			<tuple>
				<atom>Mike</atom>
			</tuple>
			<tuple>
				<atom>Willo</atom>
			</tuple>
			<tuple>
				<atom>Willy</atom>
			</tuple>
		</table>
	</tuple>
	<!--Third record-->
	<tuple>
		<atom name="NamPartyType">Person</atom>
		<atom name="NamFirst">Wilbur</atom>
		<atom name="NamLast">Withers</atom>
		<table name="NamRoles_tab">
			<!--Even if there are empty values in a table list, the tuple must be specified-->
			<tuple>
			</tuple>
			<tuple>
				<atom>Musician</atom>
			</tuple>
		</table>
	</tuple>
</table>